﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
using System.Security.Cryptography;
using CeA.Reports.Atendimento.DataAccess.DTO;
using System.Data;

namespace CeA.Reports.Atendimento.DataAccess.DAO.Oracle {
    /// <summary>
    /// 
    /// </summary>
    public class OracleAuthUsuarioImpl : CeA.Reports.Atendimento.DataAccess.DAO.AuthUsuario {
        OracleConnection m_connection = null;

        /// <summary>
        /// Initializes a new instance of the <see cref="OracleAuthUsuarioImpl"/> class.
        /// </summary>
        public OracleAuthUsuarioImpl()                                      {
            m_connection = OracleDAOFactory.CreateConnection();
        }
        

        /// <summary>
        /// Valida um usuário contra o banco de dados e carrega todas suas ROLES.
        /// </summary>
        /// <param name="usuario">login do usuário</param>
        /// <param name="senha">senha do usuário</param>
        public UsuarioDTO Validar(string usuario, string senha)             {
            OracleCommand cmd = m_connection.CreateCommand();
            StringBuilder sql = new StringBuilder();
            string pwdhash = calcularHashSenha(senha);

            sql.AppendFormat("SELECT  usr.id, usr.login, rol.rolename        \n");
            sql.AppendFormat("FROM    FaUsers usr                            \n");
            sql.AppendFormat("LEFT    JOIN                                   \n");
            sql.AppendFormat("        FaUserRoles rol on usr.id = rol.userid \n");
            sql.AppendFormat("WHERE   usr.login    = :login                  \n");
            sql.AppendFormat("AND     usr.password = :passw                  \n");

            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql.ToString();

            cmd.Parameters.AddWithValue(":login", usuario);
            cmd.Parameters.AddWithValue(":passw", pwdhash);

            OracleDataReader odr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            UsuarioDTO dto = null;
            
            if (odr.Read()) {
                dto = new UsuarioDTO();

                dto.Id = Convert.ToInt32(odr["id"]);
                dto.Login = odr["login"].ToString();
                dto.Senha = "********";
                //dto.addRole(odr["rolename"].ToString());

                //while (odr.Read())
                    //dto.addRole(odr["rolename"].ToString());
            }

            if (dto == null)
                throw new InvalidOperationException("Usuário ou senha inválidos.");

            return dto;
        }
        

        /// <summary>
        /// Troca a senha de um usuário.
        /// </summary>
        /// <param name="usuario">Objeto <see cref="LoginDTO"/> contendo os dados do usuario que vai atualizar a senha</param>
        /// <param name="novaSenha">A nova senha do usuário.</param>
        public UsuarioDTO TrocarSenha(UsuarioDTO usuario, string novaSenha) {
            OracleCommand cmd = m_connection.CreateCommand();
            UsuarioDTO dto = Validar(usuario.Login, usuario.Senha);
            string pwdhash = calcularHashSenha(novaSenha);

            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "UPDATE FaUsers usr SET usr.password = :passwd WHERE usr.id = :userid";

            cmd.Parameters.AddWithValue(":userid", dto.Id);
            cmd.Parameters.AddWithValue(":passwd", pwdhash);

            int ret = cmd.ExecuteNonQuery();

            if (ret == 0)
                throw new InvalidOperationException("Não foi possivel alterar a senha do usuario.");

            return dto;
        }
        

        /// <summary>
        /// Cria o usuário especificado.
        /// </summary>
        /// <param name="usuario">The usuario.</param>
        public UsuarioDTO Criar(UsuarioDTO usuario)                         {
            OracleCommand cmd = m_connection.CreateCommand();
            StringBuilder sql = new StringBuilder();
            string pwdhash = calcularHashSenha(usuario.Senha);

            sql.AppendFormat("insert into fausers (id, login, password)    \n");
            sql.AppendFormat("values (fausers_seq.nextval, :login, :passw) \n");
            sql.AppendFormat("returning id into :userid                    \n");
            
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql.ToString();

            cmd.Parameters.AddWithValue(":login", usuario.Login);
            cmd.Parameters.AddWithValue(":passw", pwdhash);
            cmd.Parameters.Add(":userid", OracleType.Int32).Direction = ParameterDirection.ReturnValue;
            cmd.ExecuteNonQuery();
            
            usuario.Id = Convert.ToInt32(cmd.Parameters[":userid"]);

            /*
            if (usuario.getRoles().Length > 0) {
                
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue(":userid", usuario.Id);
                cmd.Parameters.AddWithValue(":rolename", string.Empty);

                string[] roles = usuario.getRoles();
                for (int i = 0; i < roles.Length; i++) {
                    cmd.CommandText = "INSERT INTO FaUserRoles(userid, rolename) VALUES (:userid, :rolename)";
                    cmd.Parameters["rolename"].Value = roles[i];
                    cmd.ExecuteNonQuery();
                }
            }*/

            return usuario;
        }
        

        /// <summary>
        /// Carrega todas as roles de um usuário.
        /// </summary>
        /// <param name="usuario">usuario para carregar as roles.</param>
        public string[] CarregarRoles(string login)                         {
            OracleCommand cmd = m_connection.CreateCommand();
            StringBuilder sql = new StringBuilder(85);

            sql.AppendFormat("SELECT rol.rolename             \n");
            sql.AppendFormat("FROM   fausers usr              \n");
            sql.AppendFormat("LEFT   JOIN fauserroles rol     \n");
            sql.AppendFormat("       ON usr.id = rol.userid   \n");
            sql.AppendFormat("WHERE  usr.login = lower(:login)  ");

            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql.ToString();

            cmd.Parameters.AddWithValue(":login", login);
            OracleDataReader odr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            List<string> roles = new List<string>();

            while(odr.Read())
                roles.Add(odr["rolename"].ToString());

            return roles.ToArray();
        }


        /// <summary>
        /// Calcular o hash MD5 da senha de um usuário.
        /// </summary>
        /// <param name="senha">The senha.</param>
        /// <returns></returns>
        private string calcularHashSenha(string senha)                      {
            MD5 md5 = MD5.Create();
            byte[] input = Encoding.ASCII.GetBytes(senha);
            byte[] hash = md5.ComputeHash(input);

            StringBuilder hex = new StringBuilder();
            for (int i = 0; i < hash.Length; i++)
                hex.Append(hash[i].ToString("X2"));

            return hex.ToString();
        }
    }
}
